package ttt;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

public class OKvoiceUser {

    Map<String, ArrayList> map;

    public Map<String, ArrayList> selectOVoiceUser(String sqlends){

        map = new HashMap<>();
        String sql = "SELECT\n" +
                "\tphone,\n" +
                "\tok_number,\n" +
                "\timg_path,\n" +
                "\t`name`,\n" +
                "\tcountry,\n" +
                "\tsex,\n" +
                "\tpay_password,\n" +
                "\t`password` \n" +
                "FROM\n" +
                "\tusers \n" +
                "\tWHERE id <=376849 OR id > 396849\n" +
                "ORDER BY\n" +
                "\tok_number "+sqlends;

        DBHelper dbHelper = new DBHelper(sql);

        try {

            ResultSet resultSet = dbHelper.pst.executeQuery();

            int i = 1;
            while (resultSet.next()){
                ArrayList<Object> list = new ArrayList<>();
                list.add(resultSet.getString("phone"));
                list.add(resultSet.getString("ok_number"));
                list.add(resultSet.getString("img_path"));
                list.add(resultSet.getString("name"));
                list.add(resultSet.getString("country"));
                list.add(resultSet.getString("sex"));
                list.add(resultSet.getString("pay_password"));
                list.add(resultSet.getString("password"));

                map.put(resultSet.getString("ok_number"),list);

                i++;
            }

            //close
            dbHelper.close();
            resultSet.close();

        } catch (SQLException troubles) {
            troubles.printStackTrace();
        }

        return map;
    }

//    Mm Nn

    public Map<String, ArrayList> selectNewMallUser(String sqlends){
        map = new HashMap<>();

        String sql = "SELECT id,`level` FROM new_mall_user WHERE id = (SELECT id FROM users WHERE ok_number = "+"\""+sqlends+"\")";

        DBHelper dbHelper = new DBHelper(sql);

        try {

            ResultSet resultSet = dbHelper.pst.executeQuery();

            int i = 1;
            ArrayList<Object> list = new ArrayList<>();
            if (resultSet.next()){
                list.add(String.valueOf(resultSet.getInt("id")));
                list.add(String.valueOf(resultSet.getInt("level")));

                map.put(sqlends,list);

                i++;
            }else {
                list.add(0);
                list.add(0);
                map.put(sqlends,list);
            }

            //close
            dbHelper.close();
            resultSet.close();

        } catch (SQLException troubles) {
            troubles.printStackTrace();
        }

        return map;
    }



    public Map<String, ArrayList> selectPGUser(String sqlEnd){

        map = new HashMap<>();

        String sql = "SELECT\n" +
                "\tphone,\n" +
                "\tinvitation_code,\n" +
                "\timg_path,\n" +
                "\tnick_name,\n" +
                "\tconcat ( city, region ) AS city,\n" +
                "\tsex,\n" +
                "\tpay_password,\n" +
                "\t\"password\" \n" +
                "FROM\n" +
                "\t\"public\".\"users\" \n" +
                "WHERE\n" +
                "\tinvitation_code IN " + sqlEnd;
        //( '100000', '10000575' )

        PGHelper pgHelper = new PGHelper(sql);

        try {

            //System.out.println("===========");
//            System.out.println(sql);

            ResultSet resultSet = pgHelper.pst.executeQuery();

            int i = 1;
            while (resultSet.next()){
                ArrayList<Object> list = new ArrayList<>();
                list.add(resultSet.getString("phone"));
                list.add(resultSet.getString("invitation_code"));
                list.add(resultSet.getString("img_path"));
                list.add(resultSet.getString("nick_name"));
                list.add(resultSet.getString("city"));
                list.add(resultSet.getString("sex"));
                list.add(resultSet.getString("pay_password"));
                list.add(resultSet.getString("password"));

                map.put(resultSet.getString("invitation_code"),list);

                i++;
            }

            //close
            pgHelper.pst.close();
            resultSet.close();

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return map;
    }



    public Map<String, ArrayList> selectPGUser2(String sqlEnd) throws SQLException {

        map = new HashMap<>();

        String sql = "SELECT \"identity\",life_vip FROM users WHERE invitation_code = '"+sqlEnd+"'";
        //( '100000', '10000575' )

        PGHelper pgHelper = new PGHelper(sql);
        ResultSet resultSet = null;

        try {

            resultSet = pgHelper.pst.executeQuery();

            int i = 1;
            ArrayList<Object> list = new ArrayList<>();
            while (resultSet.next()){

                list.add(String.valueOf(resultSet.getString("identity")));
                list.add(String.valueOf(resultSet.getString("life_vip")));

                map.put("1",list);

                i++;
            }

            if (!resultSet.next()){
                list.add(String.valueOf(0));
                list.add(String.valueOf(0));
                map.put("1",list);
            }

            //close
            pgHelper.pst.close();
            resultSet.close();

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }finally {

            if (pgHelper.pst != null){
                pgHelper.pst.close();
            }
            if (resultSet != null){
                resultSet.close();
            }
        }
        return map;
    }

    /**
     * 根据OK号码查询okVoice用户的封禁信息
     * 0 封禁中  1，解除' 3没有封禁数据
     */
    public Map<String,Integer> selectUserStatus(String sqlBefore) throws SQLException {

        Map<String,Integer> map = new HashMap<>();
        DBHelper dbHelper = new DBHelper(sqlBefore);
        ResultSet resultSet = null;
        try {

            resultSet = dbHelper.pst.executeQuery();
            int row = resultSet.getRow();
            //判断结果是否为空
            if (resultSet.next()){
                int status = resultSet.getInt("status");
                //0 封禁中  1，解除' 3没有封禁数据
                map.put("status",status);
            }else {
                map.put("status",3);
            }

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }finally {
            if (dbHelper.pst != null){
                dbHelper.pst.close();
            }
            if (resultSet != null){
                resultSet.close();
            }
        }
        return map;
    }

    /**
     * 根据OK号码查询PG用户封禁信息
     * //（0 不封禁 1 已经封禁）'
     * 	SELECT is_ban FROM users WHERE invitation_code ='105823'
     */
    public Map<String,Integer> selectPGUserStatus(String sqlBeforePG) throws SQLException {

        Map<String,Integer> pump = new HashMap<>();
        PGHelper pgHelper = new PGHelper(sqlBeforePG);
        ResultSet resultSet = null;
        try {

            resultSet = pgHelper.pst.executeQuery();
            //判断结果是否为空
            if (resultSet.next()){
                int status = resultSet.getInt("is_ban");
                //（0 不封禁 1 已经封禁）'
                pump.put("is_ban",status);
            }else {
                    pump.put("is_ban",3);
            }

        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }finally {
            if (pgHelper.pst != null){
                pgHelper.pst.close();
            }
            if (resultSet != null){
                resultSet.close();
            }
        }
        return pump;
    }
}
